﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace TuyennvLibrary.SqlClient
{
    public abstract class BaseModel<T> where T : new()
    {
        public int page;
        public int pagesize = 50;
        public int Count = 0;
        protected int index_out = -1;
        protected String table = "";
        protected SqlHelper DB = new SqlHelper("SQlConnection", true);
        public int GetCountBy(string query)
        {

            query = string.Format("select count(*) from ({0}) a", query);
            int count = int.Parse(DB.ExecuteScalar(query, null).ToString());
            return count;
        }
        public int GetCountBy(string query, SqlParameter[] parameters)
        {

            query = string.Format("select count(*) from ({0}) a", query);
            int count = int.Parse(DB.ExecuteScalar(query, parameters).ToString());
            return count;
        }
        public static string GetQueryPagging(string query, string over_orderby, int page, int pagesize)
        {
            int index1 = (page - 1) * pagesize + 1;
            int index2 = page * pagesize;
            query = string.Format("select * from (select a.*, row_number() over (order by {0}) as rn  from ({1}) a) t where rn between {2} and {3}", (over_orderby.StartsWith("a."))?over_orderby:"a."+over_orderby, query, index1, index2);
            return query;
        }
        public static string GetQueryPagging(string query, bool OrderByIsField, string over_orderby, int page, int pagesize)
        {
            int index1 = (page - 1) * pagesize + 1;
            int index2 = page * pagesize;
            query = string.Format("select * from (select a.*, row_number() over (order by {0}) as rn  from ({1}) a) t where rn between {2} and {3}", (!over_orderby.StartsWith("a.") && OrderByIsField) ? "a." + over_orderby : over_orderby, query, index1, index2);
            return query;
        }
        public DataTable GetListBy(string[] fields, object[] values, string orderby)
        {

            if (fields.Length > 0)
            {
                SqlParameter[] parameters = new SqlParameter[fields.Length];
                for (int i = 0; i < fields.Length; i++)
                {
                    parameters[i] = new SqlParameter(fields[i], values[i]);
                }
                return DB.SelectDataTable(null, table, parameters, orderby);
            }

            return DB.SelectDataTable(null, table, orderby);
        }
        public DataTable GetByField(string field, object value, string orderby)
        {

            return DB.SelectDataTable(null, table, new SqlParameter[]{
                new SqlParameter(field,value)
            }, orderby);
        }
        
        public DataTable GetByField(string field, object value)
        {

            return DB.SelectDataTable(null, table, new SqlParameter[]{
                new SqlParameter(field,value)
            }, null);
        }
        public DataTable GetListBy(List<string> fields, List<object> values, string orderby)
        {

            if (fields.Count > 0)
            {
                SqlParameter[] parameters = new SqlParameter[fields.Count];
                for (int i = 0; i < fields.Count; i++)
                {
                    parameters[i] = new SqlParameter(fields[i], values[i]);
                }
                return DB.SelectDataTable(null, table, parameters, orderby);
            }

            return DB.SelectDataTable(null, table, orderby);
        }
        public Boolean Exist(string[] fields, object[] values)
        {
            DataTable dt = GetListBy(fields, values, null);
            return dt.Rows.Count > 0;
        }
    }
        
}